This application relates generally to database query optimization and in particular to using rewrite constraints for rewriting database queries.
Databases store large amount of data that is processed in response to database queries. Typically databases generate an execution plan for the queries. The execution plan comprises various operators and order of execution of these operators so as to execute a database query. A database query optimizer typically evaluates several execution plans to select a particular query plan for execution. The query optimizer selects the query plan that the optimizer estimates as the best query plan for executing the database query.
Generating the optimal query plan is a complex process that can take significant amount of time depending on the complexity of a query. Query optimizers use statistics describing tables used in queries to determine an execution plan for the query. If the query optimizer does not have statistics describing a table used in a query, the optimizer is likely to make incorrect decisions in selecting an execution plan. For example, statistics for intermediate results generated during execution of a query are typically not available. As a result, query optimizers generate suboptimal execution plans for complex queries that require one or more intermediate results to be processed.
Often users, for example, database experts may be able to improve upon a query plan generated by an optimizer. Some optimizers allow users to provide optimizer hints. However, hints allowed by optimizers have limited expressive power. Furthermore, optimizer hints are typically not mandatory and the optimizer can chose to ignore them if the optimizer determines that it can generate better execution plans by ignoring the hints. The optimizer may ignore the hints even if the optimizer incorrectly determines that it can generate a better execution plan. As a result, conventional database systems are often unable to generate optimal (or close to optimal) execution plans for several types of database queries, thereby resulting in inefficient execution of these database queries.